package com.totoro.web;

import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.totoro.domain.Project;
import com.totoro.domain.PublicReply;
import com.totoro.domain.ReviewProject;
import com.totoro.domain.Student;
import com.totoro.domain.Tutorial;

public class TotalExcelView extends AbstractExcelView {

	@Override
	protected void buildExcelDocument(Map<String, Object> model,
			HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		
		//指导成绩
		Tutorial tutorial = (Tutorial) model.get("tutorial");
		//评阅成绩
		ReviewProject review = (ReviewProject) model.get("reviewProject");
		//答辩成绩
		PublicReply publicReply = (PublicReply) model.get("publicReply");
		
		// 毕业设计信息
		Project project = (Project) model.get("project");
		// 学生信息
		Student student = project.getStudent();
		
		//总分
		int sum = tutorial.getAchievementLevel() + tutorial.getBasicAbility() + tutorial.getWorkAbility() + tutorial.getWorkLoad() +
					review.getAchievementLevel() + review.getDocQualityScore() +
					publicReply.getReply() + publicReply.getStatement() + Integer.parseInt(publicReply.getAchievementLevel()) + Integer.parseInt(publicReply.getCompleteness());
		
		// 设置excel表文件的名字
		String excelName = student.getNo() + student.getName() + "总成绩.xls";

		response.setHeader("Content-Disposition", "attachment;filename="
				+ new String(excelName.getBytes(), "iso8859-1"));
		
		// 创建单元格样式
		HSSFCellStyle titleCellStyle = workbook.createCellStyle();
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		HSSFCellStyle contentCellStyle = workbook.createCellStyle();
		HSSFCellStyle firstCellStyle = workbook.createCellStyle();
		
		cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyle.setBorderRight(CellStyle.BORDER_THIN);
		cellStyle.setBorderTop(CellStyle.BORDER_THIN);
		
		
		
		
		// 指定单元格居中对齐
		titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		contentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		firstCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		// 指定单元格垂直居中对齐
		titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		contentCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		firstCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		titleCellStyle.setWrapText(true);
		cellStyle.setWrapText(true);
		contentCellStyle.setWrapText(true);
		
		// 设置单元格字体
		HSSFFont font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeightInPoints((short)14);
		
		HSSFFont font2 = workbook.createFont();
		font2.setFontName("宋体");
		font2.setFontHeightInPoints((short)12);
		
		titleCellStyle.setFont(font);
		cellStyle.setFont(font2);
		contentCellStyle.setFont(font2);
		firstCellStyle.setFont(font2);
		
		
		HSSFSheet sheet = workbook.createSheet("totalScore");
		int rownum = 1;
		
		//设置各列列宽
		sheet.setColumnWidth(0, 2000);// 第一列的宽度；
		sheet.setColumnWidth(1, 2000);// 第二列的宽度；
		sheet.setColumnWidth(2, 2000);// 第三列的宽度；
		sheet.setColumnWidth(3, 4000);// 第四列的宽度；
		sheet.setColumnWidth(4, 4000);// 第五列的宽度；
		sheet.setColumnWidth(5, 4000);// 第六列的宽度；
		sheet.setColumnWidth(6, 2000);// 第七列的宽度；
		sheet.setColumnWidth(7, 2000);// 第八列的宽度；
		//附表10：
		{
			HSSFRow row = sheet.createRow(rownum);
			// 设置行高度
			row.setHeight((short) 600);
			//合并第一行的1-7列为一个单元格
			sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 0, 6));
			Cell cell = row.createCell(0);
			cell.setCellValue("附表10：");
			cell.setCellStyle(firstCellStyle);
			
			rownum++;
		}
		
		//设置标题行
		{
			HSSFRow titleRow = sheet.createRow(rownum);
			// 设置行高度
			titleRow.setHeight((short) 1000);
			//合并第一行的1-7列为一个单元格
			sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 1, 7));
			Cell cell = titleRow.createCell(1);
			cell.setCellValue("山东建筑大学毕业设计（论文）成绩评分表");
			cell.setCellStyle(titleCellStyle);
			
			rownum++;
		}
		
		//院部、班级、姓名行
		{
			HSSFRow row = sheet.createRow(rownum);
			//设置行高
			row.setHeight((short)500);
			Cell cell = row.createCell(1);
			cell.setCellValue("院部：");
			cell.setCellStyle(contentCellStyle);
			
			sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, 2, 3));
			Cell cell2  = row.createCell(2);
//怎么取到院部？？？？？
			cell2.setCellValue(student.getStudentClass().getMajor().getDepartment().getSchool().getDescription());
			cell2.setCellStyle(contentCellStyle);
			
			Cell cell3 = row.createCell(4);
			cell3.setCellValue("班级：");
			cell3.setCellStyle(contentCellStyle);
			
			Cell cell4 = row.createCell(5);
			cell4.setCellValue(student.getStudentClass().getDescription());
			cell4.setCellStyle(contentCellStyle);
			
			Cell cell5 = row.createCell(6);
			cell5.setCellValue("姓名：");
			cell5.setCellStyle(contentCellStyle);
			
			Cell cell6 = row.createCell(7);
			cell6.setCellValue(student.getName());
			cell6.setCellStyle(contentCellStyle);
			
			rownum++;
		}
		
		//题目行
		{
			HSSFRow row = sheet.createRow(rownum);
			//设置行高
			row.setHeight((short)500);
			//合并1-2列 
			CellRangeAddress ca = new CellRangeAddress(rownum, rownum, 1, 2);

			sheet.addMergedRegion(ca);
			Cell cell = row.createCell(1);
			cell.setCellValue("题目");
			cell.setCellStyle(cellStyle);
addBorder(ca, sheet, workbook);			
			//合并3-7列
			CellRangeAddress ca2 = new CellRangeAddress(rownum, rownum, 3, 7);

			sheet.addMergedRegion(ca2);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue(project.getTitle());
			cell2.setCellStyle(cellStyle);
addBorder(ca2, sheet, workbook);			
			rownum++;
		}
		
		//第三行
		{
			HSSFRow row = sheet.createRow(rownum);
			//设置行高
			row.setHeight((short)500);
			//合并1-2列
CellRangeAddress ca = new CellRangeAddress(rownum, rownum, 1, 2);

			sheet.addMergedRegion(ca);
			Cell cell = row.createCell(1);
			cell.setCellValue("序号");
			cell.setCellStyle(cellStyle);
addBorder(ca, sheet, workbook);			
			//合并3-5列
CellRangeAddress ca2 = new CellRangeAddress(rownum, rownum, 3, 5);

			sheet.addMergedRegion(ca2);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue("评价内容");
			cell2.setCellStyle(cellStyle);
addBorder(ca2, sheet, workbook);	

			Cell cell3 = row.createCell(6);
			cell3.setCellValue("满分");
			cell3.setCellStyle(cellStyle);
			
			Cell cell4 = row.createCell(7);
			cell4.setCellValue("评分");
			cell4.setCellStyle(cellStyle);
			
			rownum++;
		}
		
		//指导教师评分单元
		{
			HSSFRow row = sheet.createRow(rownum);
			// 合并单元格：将1行1列1行4列的单元格合并为一个单元格

			Cell cell1 = row.createCell(2);
			cell1.setCellValue("1");
			cell1.setCellStyle(cellStyle);

			// 设置工作量、工作态度分
CellRangeAddress ca2 = new CellRangeAddress(rownum, rownum, 3, 5);

			sheet.addMergedRegion(ca2);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue("工作量、工作态度");
			cell2.setCellStyle(cellStyle);
addBorder(ca2, sheet, workbook);	

			Cell cell3 = row.createCell(6);
			cell3.setCellValue("10");
			cell3.setCellStyle(cellStyle);
			
			Cell cell4 = row.createCell(7);
			cell4.setCellValue(tutorial.getWorkLoad());
			cell4.setCellStyle(cellStyle);
			
			// 设置行高度
			row.setHeight((short) 500);

			rownum++;
		}
		
		// 设置基本理论、基本知识、基本技能和外语水平分
		{
			HSSFRow row = sheet.createRow(rownum);
			
			Cell cell1 = row.createCell(2);
			cell1.setCellValue("2");
			cell1.setCellStyle(cellStyle);

			// 设置工作量、工作态度分
CellRangeAddress ca = new CellRangeAddress(rownum, rownum, 3, 5);

			sheet.addMergedRegion(ca);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue("基本理论、基本知识、基本技能和外语水平");
			cell2.setCellStyle(cellStyle);
addBorder(ca, sheet, workbook);	

			Cell cell3 = row.createCell(6);
			cell3.setCellValue("10");
			cell3.setCellStyle(cellStyle);
			
			Cell cell4 = row.createCell(7);
			cell4.setCellValue(tutorial.getBasicAbility());
			cell4.setCellStyle(cellStyle);
			
			// 设置行高度
			row.setHeight((short) 500);

			rownum++;
		}
		
		// 设置独立工作能力、分析与解决问题的能力分
		{
			HSSFRow row = sheet.createRow(rownum);
			
			Cell cell1 = row.createCell(2);
			cell1.setCellValue("3");
			cell1.setCellStyle(cellStyle);

			// 设置独立工作能力、分析与解决问题的能力分
CellRangeAddress ca = new CellRangeAddress(rownum, rownum, 3, 5);

			sheet.addMergedRegion(ca);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue("独立工作能力、分析与解决问题的能力");
			cell2.setCellStyle(cellStyle);
addBorder(ca, sheet, workbook);	


			Cell cell3 = row.createCell(6);
			cell3.setCellValue("10");
			cell3.setCellStyle(cellStyle);
			
			Cell cell4 = row.createCell(7);
			cell4.setCellValue(tutorial.getWorkAbility());
			cell4.setCellStyle(cellStyle);
			
			// 设置行高度
			row.setHeight((short) 500);

			rownum++;
		}
		
		// 设置完成任务情况及水平分
		{
			HSSFRow row = sheet.createRow(rownum);
			HSSFRow lastRow = sheet.getRow(rownum -3);
			
			CellRangeAddress ca1 = new CellRangeAddress(rownum - 3, rownum, 1, 1);

			
			Cell cell0 = lastRow.createCell(1);
			cell0.setCellValue("指导老师评分");
			cell0.setCellStyle(cellStyle);
			sheet.addMergedRegion(ca1);
addBorder(ca1, sheet, workbook);

			
			
			Cell cell1 = row.createCell(2);
			cell1.setCellValue("4");
			cell1.setCellStyle(cellStyle);

			// 设置完成任务情况及水平分
CellRangeAddress ca = new CellRangeAddress(rownum, rownum, 3, 5);

			sheet.addMergedRegion(ca);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue("完成任务情况及水平");
			cell2.setCellStyle(cellStyle);
addBorder(ca, sheet, workbook);	

			Cell cell3 = row.createCell(6);
			cell3.setCellValue("10");
			cell3.setCellStyle(cellStyle);
			
			Cell cell4 = row.createCell(7);
			cell4.setCellValue(tutorial.getAchievementLevel());
			cell4.setCellStyle(cellStyle);
			
			// 设置行高度
			row.setHeight((short) 500);

			rownum++;
		}
		
		//评阅人评分单元
		{
			HSSFRow row = sheet.createRow(rownum);
			// 合并单元格：将1行1列1行4列的单元格合并为一个单元格


			Cell cell1 = row.createCell(2);
			cell1.setCellValue("5");
			cell1.setCellStyle(cellStyle);

			// 设置工作量、论文质量分
CellRangeAddress ca2 = new CellRangeAddress(rownum, rownum, 3 , 5);

			sheet.addMergedRegion(ca2);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue("工作量、论文质量");
			cell2.setCellStyle(cellStyle);
			
addBorder(ca2, sheet, workbook);

			Cell cell3 = row.createCell(6);
			cell3.setCellValue("10");
			cell3.setCellStyle(cellStyle);
			
			Cell cell4 = row.createCell(7);
			cell4.setCellValue(review.getDocQualityScore());
			cell4.setCellStyle(cellStyle);
			
			// 设置行高度
			row.setHeight((short) 500);

			rownum++;
		}
		
		// 设置成果技术水平分
		{
			HSSFRow row = sheet.createRow(rownum);
			
			HSSFRow lastRow = sheet.getRow(rownum -1);
			CellRangeAddress ca1 = new CellRangeAddress(rownum -1 , rownum , 1, 1);
			
						
						Cell cell0 = lastRow.createCell(1);
						cell0.setCellValue("评阅人评分");
						cell0.setCellStyle(cellStyle);
						sheet.addMergedRegion(ca1);
			addBorder(ca1, sheet, workbook);
			
			Cell cell1 = row.createCell(2);
			cell1.setCellValue("6");
			cell1.setCellStyle(cellStyle);

			// 设置工作量、工作态度分
CellRangeAddress ca = new CellRangeAddress(rownum, rownum, 3, 5);

			sheet.addMergedRegion(ca);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue("成果技术水平");
			cell2.setCellStyle(cellStyle);
addBorder(ca, sheet, workbook);	

			Cell cell3 = row.createCell(6);
			cell3.setCellValue("10");
			cell3.setCellStyle(cellStyle);
			
			Cell cell4 = row.createCell(7);
			cell4.setCellValue(review.getAchievementLevel());
			cell4.setCellStyle(cellStyle);
			
			// 设置行高度
			row.setHeight((short) 500);

			rownum++;
		}
		
		//答辩小组评分单元
		{
			HSSFRow row = sheet.createRow(rownum);
			// 合并单元格：将1行1列1行4列的单元格合并为一个单元格


			Cell cell1 = row.createCell(2);
			cell1.setCellValue("7");
			cell1.setCellStyle(cellStyle);

			// 设置工作量、论文质量分
CellRangeAddress ca2 = new CellRangeAddress(rownum, rownum, 3, 5);

			sheet.addMergedRegion(ca2);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue("完成任务书规定的要求与水平");
			cell2.setCellStyle(cellStyle);
addBorder(ca2, sheet, workbook);		

			Cell cell3 = row.createCell(6);
			cell3.setCellValue("10");
			cell3.setCellStyle(cellStyle);
			
			Cell cell4 = row.createCell(7);
			cell4.setCellValue(publicReply.getCompleteness());
			cell4.setCellStyle(cellStyle);
			
			// 设置行高度
			row.setHeight((short) 500);

			rownum++;
		}
		
		// 设置设计、论文、软件及实物的质量分
		{
			HSSFRow row = sheet.createRow(rownum);
			
			Cell cell1 = row.createCell(2);
			cell1.setCellValue("8");
			cell1.setCellStyle(cellStyle);

			// 设置工作量、工作态度分
			CellRangeAddress ca = new CellRangeAddress(rownum, rownum, 3, 5);
			
			sheet.addMergedRegion(ca);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue("设计、论文、软件及实物的质量");
			cell2.setCellStyle(cellStyle);
			addBorder(ca, sheet, workbook);
			
			Cell cell3 = row.createCell(6);
			cell3.setCellValue("10");
			cell3.setCellStyle(cellStyle);
			
			Cell cell4 = row.createCell(7);
			cell4.setCellValue(publicReply.getAchievementLevel());
			cell4.setCellStyle(cellStyle);
			
			// 设置行高度
			row.setHeight((short) 500);

			rownum++;
		}
		
		// 设置独立工作能力、分析与解决问题的能力分
		{
			HSSFRow row = sheet.createRow(rownum);
			
			Cell cell1 = row.createCell(2);
			cell1.setCellValue("9");
			cell1.setCellStyle(cellStyle);

			CellRangeAddress ca = new CellRangeAddress(rownum, rownum, 3, 5);
			
			sheet.addMergedRegion(ca);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue("答辩讲述");
			cell2.setCellStyle(cellStyle);
			addBorder(ca, sheet, workbook);
			
			Cell cell3 = row.createCell(6);
			cell3.setCellValue("10");
			cell3.setCellStyle(cellStyle);
			
			Cell cell4 = row.createCell(7);
			cell4.setCellValue(publicReply.getStatement());
			cell4.setCellStyle(cellStyle);
			
			// 设置行高度
			row.setHeight((short) 500);

			rownum++;
		}
		
		// 设置完成任务情况及水平分
		{
			HSSFRow row = sheet.createRow(rownum);
			HSSFRow lastRow = sheet.getRow(rownum -3);
			CellRangeAddress ca1 = new CellRangeAddress(rownum - 3, rownum, 1, 1);
			
						
						Cell cell0 = lastRow.createCell(1);
						cell0.setCellValue("答辩小组评分");
						cell0.setCellStyle(cellStyle);
						sheet.addMergedRegion(ca1);
			addBorder(ca1, sheet, workbook);
			
			Cell cell1 = row.createCell(2);
			cell1.setCellValue("10");
			cell1.setCellStyle(cellStyle);

			// 设置完成任务情况及水平分
			CellRangeAddress ca = new CellRangeAddress(rownum, rownum, 3, 5);
			
			sheet.addMergedRegion(ca);
			Cell cell2 = row.createCell(3);
			cell2.setCellValue("回答问题的正确性");
			cell2.setCellStyle(cellStyle);
			addBorder(ca, sheet, workbook);
			
			Cell cell3 = row.createCell(6);
			cell3.setCellValue("10");
			cell3.setCellStyle(cellStyle);
			
			Cell cell4 = row.createCell(7);
			cell4.setCellValue(publicReply.getReply());
			cell4.setCellStyle(cellStyle);
			
			// 设置行高度
			row.setHeight((short) 500);

			rownum++;
		}
		
		//总分行
		{
			HSSFRow row = sheet.createRow(rownum);
			// 合并单元格：将1行1列1行4列的单元格合并为一个单元格
			CellRangeAddress ca = new CellRangeAddress(rownum, rownum, 1, 6);
			
			sheet.addMergedRegion(ca);
			Cell cell0 = row.createCell(1);
			cell0.setCellValue("总分");
			cell0.setCellStyle(cellStyle);
			addBorder(ca, sheet, workbook);
			
			Cell cell1 = row.createCell(7);
			cell1.setCellValue(sum);
			cell1.setCellStyle(cellStyle);
			
			row.setHeight((short)500);
			
			rownum++;
		}
		
		{
			HSSFRow row = sheet.createRow(rownum);
			row.setHeight((short)300);
			
			rownum++;
		}
		// 答辩委员会负责人签字
		{
			HSSFRow row = sheet.createRow(rownum);
			HSSFRow row2 = sheet.createRow(rownum + 1);
			row.setHeight((short)700);
			row2.setHeight((short)700);
			sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+1 , 3, 4));
			
			Cell cell0 = row.createCell(3);
			cell0.setCellValue("答辩委员会负责人签字：");
			cell0.setCellStyle(contentCellStyle);
			
//此处得到具体图片路径
//			URL url = new URL("http://b.hiphotos.baidu.com/album/w%3D2048/sign=c52ac5dd95eef01f4d141fc5d4c69825/94cad1c8a786c9170e9b2e80c83d70cf3ac757ea.jpg"); 
//            URLConnection uc = url.openConnection(); 
//			InputStream is = uc.getInputStream();
//		    byte[] bytes = IOUtils.toByteArray(is);
		    //用户不允许打印院长签字章
//		    int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
//		    is.close();

		    CreationHelper helper = workbook.getCreationHelper();


		    Drawing drawing = sheet.createDrawingPatriarch();

		    ClientAnchor anchor = helper.createClientAnchor();
		    
		    anchor.setCol1(5);
		    anchor.setRow1(rownum);
		    anchor.setCol2(8);
		    anchor.setRow2(rownum+2);
		
		    
//		    Picture pict = drawing.createPicture(anchor, pictureIdx);

		    //auto-size picture relative to its top-left corner
		   // pict.resize();
			
			
			rownum++;
			rownum++;
		}
		
		//年月日
		{
			HSSFRow row = sheet.createRow(rownum);
			//
			sheet.addMergedRegion(new CellRangeAddress(rownum, rownum , 5, 7));
			
			Cell cell0 = row.createCell(5);
			cell0.setCellValue(new SimpleDateFormat("yyyy 年 MM 月 dd 日").format(new Date()));
			cell0.setCellStyle(contentCellStyle);
			
			row.setHeight((short)600);
			
			rownum++;
		}
	}
	
	private void addBorder(CellRangeAddress ca, HSSFSheet sheet, HSSFWorkbook workbook) {
		
		//多行多列合并时的情况
		HSSFFont font = workbook.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short)12);
			//左边
			HSSFCellStyle leftCellStyle = workbook.createCellStyle();
			leftCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			leftCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			leftCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			leftCellStyle.setFont(font);
			leftCellStyle.setWrapText(true);
			//右边
			HSSFCellStyle rightCellStyle = workbook.createCellStyle();
			rightCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			rightCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			rightCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			rightCellStyle.setFont(font);
			rightCellStyle.setWrapText(true);
			//下中
			HSSFCellStyle downMidCellStyle = workbook.createCellStyle();
			downMidCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			downMidCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			downMidCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			downMidCellStyle.setFont(font);
			downMidCellStyle.setWrapText(true);
			//左下
			HSSFCellStyle leftDownCellStyle = workbook.createCellStyle();
			leftDownCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			leftDownCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			leftDownCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			leftDownCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			leftDownCellStyle.setFont(font);
			leftDownCellStyle.setWrapText(true);
			//右下
			HSSFCellStyle rightDownCellStyle = workbook.createCellStyle();
			rightDownCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			rightDownCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			rightDownCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			rightDownCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			rightDownCellStyle.setFont(font);
			rightDownCellStyle.setWrapText(true);
			//左上
			HSSFCellStyle leftUpCellStyle = workbook.createCellStyle();
			leftUpCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			leftUpCellStyle.setBorderTop(CellStyle.BORDER_THIN);
			leftUpCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			leftUpCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			leftUpCellStyle.setFont(font);
			leftUpCellStyle.setWrapText(true);
			//右上
			HSSFCellStyle rightUpCellStyle = workbook.createCellStyle();
			rightUpCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			rightUpCellStyle.setBorderTop(CellStyle.BORDER_THIN);
			rightUpCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			rightUpCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			rightUpCellStyle.setFont(font);
			rightUpCellStyle.setWrapText(true);
			//上中
			HSSFCellStyle upMidCellStyle = workbook.createCellStyle();
			upMidCellStyle.setBorderTop(CellStyle.BORDER_THIN);
			upMidCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			upMidCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			upMidCellStyle.setFont(font);
			upMidCellStyle.setWrapText(true);
		//单行多列情况
		
			HSSFCellStyle oneRowLeftCellStyle = workbook.createCellStyle();
			oneRowLeftCellStyle.setBorderTop(CellStyle.BORDER_THIN);
			oneRowLeftCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			oneRowLeftCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			oneRowLeftCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			oneRowLeftCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			oneRowLeftCellStyle.setFont(font);
			oneRowLeftCellStyle.setWrapText(true);
			
			
			HSSFCellStyle oneRowMidCellStyle = workbook.createCellStyle();
			oneRowMidCellStyle.setBorderTop(CellStyle.BORDER_THIN);
			oneRowMidCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			oneRowMidCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			oneRowMidCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			oneRowMidCellStyle.setFont(font);
			oneRowMidCellStyle.setWrapText(true);
			
			
			HSSFCellStyle oneRowRightCellStyle = workbook.createCellStyle();
			oneRowRightCellStyle.setBorderTop(CellStyle.BORDER_THIN);
			oneRowRightCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			oneRowRightCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			oneRowRightCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			oneRowRightCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			oneRowRightCellStyle.setFont(font);
			oneRowRightCellStyle.setWrapText(true);
			
		//多行单列情况
			HSSFCellStyle oneColUpCellStyle = workbook.createCellStyle();
			oneColUpCellStyle.setBorderTop(CellStyle.BORDER_THIN);
			oneColUpCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			oneColUpCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			oneColUpCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			oneColUpCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			oneColUpCellStyle.setFont(font);
			oneColUpCellStyle.setWrapText(true);
			
			
			HSSFCellStyle oneColMidCellStyle = workbook.createCellStyle();
			oneColMidCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			oneColMidCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			oneColMidCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			oneColMidCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			oneColMidCellStyle.setFont(font);
			oneColMidCellStyle.setWrapText(true);
			
			HSSFCellStyle oneColDownCellStyle = workbook.createCellStyle();
			oneColDownCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			oneColDownCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			oneColDownCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			oneColDownCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			oneColDownCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			oneColDownCellStyle.setFont(font);
			oneColDownCellStyle.setWrapText(true);
			
		int firstRow = ca.getFirstRow();
		int firstCol = ca.getFirstColumn();
		int lastRow = ca.getLastRow();
		int lastCol = ca.getLastColumn();
//System.out.println(firstRow + "," + lastRow + "," + firstCol + "," + lastCol);
		
		//单行
		if (firstRow == lastRow) {
			HSSFRow row = sheet.getRow(firstRow);
			for (int i = firstCol; i <= lastCol; i++) {
				
				HSSFCell cell = row.getCell(i);
				if (cell == null) {
					cell = row.createCell(i);
				}
System.out.println(cell);
				if (i == firstCol) {
					cell.setCellStyle(oneRowLeftCellStyle);
				} else if (i == lastCol) {
					cell.setCellStyle(oneRowRightCellStyle);
				} else {
					cell.setCellStyle(oneRowMidCellStyle);
				}
			}//单列
		} else if (firstCol == lastCol) {
			
			for (int i = firstRow; i <= lastRow; i++) {
				HSSFRow row = sheet.getRow(i);
System.out.println(row);
				HSSFCell cell = row.getCell(firstCol);
				if (cell == null) {
					cell = row.createCell(firstCol);
				}
				if (i == firstRow) {
					
					cell.setCellStyle(oneColUpCellStyle);
				} else if (i == lastRow) {
					cell.setCellStyle(oneColDownCellStyle);
				} else {
					cell.setCellStyle(oneColMidCellStyle);
				}
			}
		} else {
			for(int i = firstRow; i <= lastRow; i++) {
				HSSFRow row = sheet.getRow(i);
				if (i == firstRow) {
					for (int j = firstCol; j <= lastCol; j++) {
						HSSFCell cell = row.getCell(j);
						if (cell == null) {
							cell = row.createCell(j);
						}
						if (j == firstCol) {
							cell.setCellStyle(leftUpCellStyle);
						} else if (j == lastCol) {
							cell.setCellStyle(rightUpCellStyle);
						} else {
							cell.setCellStyle(upMidCellStyle);
						}
					}
				} else if (i == lastRow) {
					for (int j = firstCol; j <= lastCol; j++) {
						HSSFCell cell = row.getCell(j);
						if (cell == null) {
							cell = row.createCell(j);
						}
						if (j == firstCol) {
							cell.setCellStyle(leftDownCellStyle);
						} else if (j == lastCol) {
							cell.setCellStyle(rightDownCellStyle);
						} else {
							cell.setCellStyle(downMidCellStyle);
						}
					}
				} else {
					for (int j = firstCol; j <= lastCol; j++) {
						HSSFCell cell = row.getCell(j);
						if (cell == null) {
							cell = row.createCell(j);
						}
						if (j == firstCol) {
							cell.setCellStyle(leftCellStyle);
						} else if (j == lastCol) {
							cell.setCellStyle(rightCellStyle);
						} 
					}
				}
				
				
			}
		}
		
		
	}

}
